using System;
using System.Data;
using System.Data.Sql;
using System.Data.SqlClient;
using System.Collections.Generic;
using System.Text;

namespace StudentManagementApplication.DAO
{
    class SubjectDAO : BaseDAO
    {
        public SubjectDAO()
        {
        }

        public Entities.Subject CreateSubjectFromReader(IDataReader reader)
        {
            Entities.Subject subject = new Entities.Subject();
            subject.SubjectID = (string)reader["cSubjectID"];
            subject.SubjectName = (string)reader["vSubjectName"];
            subject.CourseID = (string)reader["cCourseID"];
            return subject;
        }

        public void InsertSubject(Entities.Subject subject)
        {
            using (SqlConnection connection = GetConnection())
            {
                SqlCommand command = new SqlCommand("prcInsertSubject", connection);
                command.CommandType = CommandType.StoredProcedure;

                command.Parameters.Add(new SqlParameter("@vSubjectName", subject.SubjectName));
                command.Parameters.Add(new SqlParameter("@cCourseID", subject.CourseID));
                connection.Open();
                command.ExecuteNonQuery();
                //if (command.ExecuteNonQuery() <= 0)

            }
        }

        public void UpdateSubject(Entities.Subject subject)
        {
            using (SqlConnection connection = GetConnection())
            {
                SqlCommand command = new SqlCommand("prcUpdateSubject", connection);
                command.CommandType = CommandType.StoredProcedure;

                command.Parameters.Add(new SqlParameter("@cSubjectID", subject.SubjectID));
                command.Parameters.Add(new SqlParameter("@vSubjectName", subject.SubjectName));
                command.Parameters.Add(new SqlParameter("@cCourseID", subject.CourseID));

                connection.Open();
                command.ExecuteNonQuery();
            }
        }

        public void DeleteSubject(string sSubjectID)
        {
            using (SqlConnection connection = GetConnection())
            {
                SqlCommand command = new SqlCommand("prcDeleteSubject", connection);
                command.CommandType = CommandType.StoredProcedure;

                command.Parameters.Add(new SqlParameter("@cSubjectID", sSubjectID));

                connection.Open();
                command.ExecuteNonQuery();
            }
        }

        public List<Entities.Subject> GetAllSubjects()
        {
            using (SqlConnection connection = GetConnection())
            {
                SqlCommand command = new SqlCommand("prcGetAllSubjects", connection);
                command.CommandType = CommandType.StoredProcedure;

                connection.Open();
                List<Entities.Subject> subjects = new List<Entities.Subject>();
                using (IDataReader reader = command.ExecuteReader(CommandBehavior.CloseConnection))
                {
                    while (reader.Read())
                    {
                        subjects.Add(CreateSubjectFromReader(reader));
                    }
                    //else
                    //    throw;
                }
                return subjects;
            }
        }

        public Entities.Subject GetSubjectBySubjectID(string sSubjectID)
        {
            using (SqlConnection connection = GetConnection())
            {
                SqlCommand command = new SqlCommand("prcGetSubjectBySubjectID", connection);
                command.CommandType = CommandType.StoredProcedure;

                command.Parameters.Add(new SqlParameter("@cSubjectID", sSubjectID));

                connection.Open();
                Entities.Subject subject = null;
                using (IDataReader reader = command.ExecuteReader(CommandBehavior.CloseConnection))
                {
                    if (reader.Read())
                    {
                        subject = CreateSubjectFromReader(reader);
                    }
                }
                return subject;
            }
        }

        public List<Entities.Subject> GetSubjectByClassID(string sClassID)
        {
            using (SqlConnection connection = GetConnection())
            {
                SqlCommand command = new SqlCommand("prcGetSubjectByClassID", connection);
                command.CommandType = CommandType.StoredProcedure;

                command.Parameters.Add(new SqlParameter("@cClassID", sClassID));
                connection.Open();
                List<Entities.Subject> subjects = new List<Entities.Subject>();
                using (IDataReader reader = command.ExecuteReader(CommandBehavior.CloseConnection))
                {
                    while (reader.Read())
                    {
                        subjects.Add(CreateSubjectFromReader(reader));
                    }
                    //else
                    //    throw;
                }
                return subjects;
            }
        }

        public List<Entities.Subject> GetSubjectByStaffID(string sStaffID)
        {
            using (SqlConnection connection = GetConnection())
            {
                SqlCommand command = new SqlCommand("prcGetSubjectByStaffID", connection);
                command.CommandType = CommandType.StoredProcedure;

                command.Parameters.Add(new SqlParameter("@cStaffID", sStaffID));
                connection.Open();
                List<Entities.Subject> subjects = new List<Entities.Subject>();
                using (IDataReader reader = command.ExecuteReader(CommandBehavior.CloseConnection))
                {
                    while (reader.Read())
                    {
                        subjects.Add(CreateSubjectFromReader(reader));
                    }
                    //else
                    //    throw;
                }
                return subjects;
            }
        }

    }
}
